package buildinsertdictsql;

import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.util.StrUtil;
import file.FileUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class BuildInsertDictSql {
    static String output = "D:\\临时\\dict_"+ LocalDateTimeUtil.format(LocalDateTime.now(),"yyMMddHHmmss") +".sql";
    // static String output = "D:\\JavaProject\\pcitech\\pci-cipos\\cipos-work\\src\\main\\resources\\db\\migration\\V2.0.0_20240803103030__全量字典初始化.sql";
    static String errorDictOutput = "D:\\临时\\errorDict_"+ LocalDateTimeUtil.format(LocalDateTime.now(),"yyMMddHHmmss")+".txt";

    public static void main(String[] args) {
        // String url = "jdbc:mysql://localhost:3306/pd_dicmanage_c?generateSimpleParameterMetadata=true&serverTimezone=Asia/Shanghai&useSSL=false";
        // String username = "root";
        // String password = "root";

        // String url = "jdbc:mysql://172.25.22.250:3306/pd_dicmanage?generateSimpleParameterMetadata=true&serverTimezone=Asia/Shanghai&useSSL=false";
        // String username = "videoweb";
        // String password = "suntek";

        String url = "jdbc:mysql://172.25.22.26:3306/pd_dicmanage?generateSimpleParameterMetadata=true&serverTimezone=Asia/Shanghai&useSSL=false";
        String username = "videoweb";
        String password = "suntek";

        try(Connection conn = DriverManager.getConnection(url, username, password)) {
            List<Integer> kinds = getDictKinds(conn);
            for (Integer kind : kinds) {
                List<String> sqlContent = getInsertDictSql(conn, kind);
                sqlContent.add("");
                FileUtil.write(output, sqlContent);
            }

            // List<String> sqlContent = getInsertDictSql(conn, 3120);
            // sqlContent.add("");
            // FileUtil.write(output, sqlContent);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static List<String> getInsertDictSql(Connection conn, Integer kind) throws SQLException {
        List<String> list = new ArrayList<>();

        List<SysDict> dictList = selectDict(conn, kind);

        // 检查异常字典：只有父级
        if (dictList.size() == 1) {
            FileUtil.write(errorDictOutput, Arrays.asList(dictList.get(0).getKind()+""));
        }

        list.add("-- " + dictList.get(0).getKind() + " " + dictList.get(0).getName());
        list.add("delete from `pd_dicmanage`.`SYS_DICT` where `KIND` = "+kind+";");
        list.add("INSERT INTO `pd_dicmanage`.`SYS_DICT`(`ID`,`PARENT_ID`,`NAME`,`CODE`,`KIND`,`PARENT_FULL_NAME`,`DESCRIPTION`,`CREATE_TIME`,`UPDATE_TIME`,`IS_SYS_DEFAULT`,`DEFINE`,`SORT_NUM`,`SHORT_NAME`) VALUES ");
        for (int i = 0; i < dictList.size(); i++) {
            SysDict dict = dictList.get(i);
            list.add(String.format("(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)%s",
                    dict.getParentId()==-1?dict.getId():"null", // parent级插入id，child自增插入null
                    getParentId(dict, dictList),
                    "'"+dict.getName()+"'",
                    "'"+dict.getCode()+"'",
                    dict.getKind(),
                    dict.getParentFullName()!=null?"'"+dict.getParentFullName()+"'":"null",
                    dict.getDescription()!=null?"'"+dict.getDescription()+"'":"null",
                    StrUtil.isNotBlank(dict.getCreateTime())?"'"+dict.getCreateTime()+"'":"null",
                    StrUtil.isNotBlank(dict.getUpdateTime())?"'"+dict.getUpdateTime()+"'":"null",
                    dict.getIsSysDefault(),
                    dict.getDefine(),
                    dict.getSortNum()!=null?dict.getSortNum():"null",
                    dict.getShortName()!=null?"'"+dict.getShortName()+"'":"null",
                    i==dictList.size()-1?";":","
                    ));
        }
        return list;
    }

    private static String getParentId(SysDict curDict, List<SysDict> dictList) {
        if (curDict.getParentId()==-1){// 顶级
            return curDict.getParentId()+"";
        }
        if (curDict.getParentId().equals(curDict.getKind())){// 子级
            return curDict.getParentId()+"";
        }
        for (SysDict sysDict : dictList) {// 只考虑一个kind中所有code都不相同的情况
            if (curDict.getParentId().equals(sysDict.getId())) {
                return String.format("(select t1.id from `pd_dicmanage`.`SYS_DICT` t1 where t1.kind=%s and t1.code='%s')",
                            sysDict.getKind(),sysDict.getCode()
                        );
            }
        }
        throw new RuntimeException("找不到父级");
    }

    private static List<SysDict> selectDict(Connection conn, Integer kind) throws SQLException {
        List<SysDict> list = new ArrayList<>();
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select * from sys_dict where kind=" + kind)) {
            while (rs.next()) {
                list.add(toEntity(rs));
            }
        }
        return list;
    }

    private static SysDict toEntity(ResultSet rs) throws SQLException {
        SysDict dict = new SysDict();
        dict.setId(rs.getInt("ID")); // not null
        dict.setParentId(rs.getInt("PARENT_ID")); // not null
        dict.setName(rs.getString("NAME")); // not null
        dict.setCode(rs.getString("CODE")); // not null
        dict.setKind(rs.getInt("KIND")); // not null
        dict.setParentFullName(rs.getString("PARENT_FULL_NAME"));
        dict.setDescription(rs.getString("DESCRIPTION"));
        dict.setCreateTime(rs.getString("CREATE_TIME")); // not null
        dict.setUpdateTime(rs.getString("UPDATE_TIME")); // not null
        dict.setIsSysDefault(rs.getInt("IS_SYS_DEFAULT")); // default 0
        dict.setDefine(rs.getInt("DEFINE")); // not null
        dict.setSortNum(rs.getObject("SORT_NUM") != null ? rs.getInt("SORT_NUM") : null); // nullable
        dict.setShortName(rs.getString("SHORT_NAME"));
        return dict;
    }

    private static List<Integer> getDictKinds(Connection conn) throws SQLException {
        List<Integer> list = new ArrayList<>();
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select distinct kind from dictkind order by kind")) {
            while (rs.next()) {
                list.add(rs.getInt("kind"));
            }
        }
        return list;
    }
}
